I everyone in this picture.
We will learn how to play lookup functions index and match Trojans first.
If will look up function at Stanford where to go look up this function will search the value that we
specify vertical in a table and then return another value corresponding to back value from the table.
I know that the definition is not very clear.
So let us look at this example to understand this clearly supposing the small group of five students
that we have been studying you want to add the music scores for these students from the music teachers
to even the music teacher has a restaurant in the list also the task of matching each student and assigning
marks will be very cumbersome.
If we have a lot of student.
This is where we look up will help us to use.
We look up a is equal do we look up selective value.
We want to match little league table.
We just do we differ.
Then mentioned the column number from which we want this response.
This is the second column we want to respond to.
Second column which contains marks the fourth parameter is the type of match we want if we want exact
match.
There should be zero for approximate match.
This should be one.
We want an exact match for Turin story in one value table.
So we will use 0 percent of we see that it went to the table selected it matched in lookup value vertically
2 Student 1 hit it gave back the value in column 2 which is 45.
As we have asked them to do now we put dollar signs on the reference table values and extend a formula
to get music marks for other students.
Next is X lookup function which is horizontal look up in this the value will be looked up horizontally
in the table.
We will give the low number from which we want to respond to instead of column number.
For example if we want to add to our table marks of student six in maths science and English from another
table which contains marks of student and all this object we will type is equal to each lookup
select this object name.
Then to link the defense table
less effort to log the different table values
comma then give the draw number which is do not Case
0 4 exact match attended to fill table.
We can do the same for 4 and 7 although only the row number will be 3 instead of 2.
Please try that on your own.
Next we will look at the index function as the name suggests.
If in any given table we want to access any particular value whose index number or the row and column
numbers are known to us we use the index function.
In this example if we want to find this code of a particular student in a particular subject.
When we first started this to the number two or number then ghastly Subject Name to Column number and
then used an index function.
This will be the reference table for the index function.
You can see that's too random but actually can be used as Rule number.
So STUDENT 1 The Student 1 is actually the rule one off of our table and 2 1 4 columns match marks are
in column 1 science are in column 2 and English is in column 3.
So we know down 1 2 and 3 or what math science and English
this conversion we can do by using nested ifs as discussed previously.
Now we apply index is equal to index select the table
give rule number comma
give column number presented so you can see that first then as scored ninety nine Martin mats if we
change the number of today to do it going to be a better two sixty five.
If you change subject to science it will be a digital the.
You can try changing different values of student and subjects to get different matters.
Or good.
Next up is match function match function will return the position of a particular value in an array.
That is if you have a list of any numbers match will value the position of a particular number in that
list.
So here if you want to find the student who has scored maximum marks you need to object.
We first find the maximum Mark scored in each object using the max function.
Now we will use match function to find the position of this maximum value in the reference list.
Every type is equal to match select DL
and then click the edit.
This center
this max values default value in the list which also corresponds to strength for
similarly your play match function horizontally to identify the subject in which each student has scored
the maximum score.
Again we find the max goal using MATCH function.
We then match this max value with the table selected horizontally to get the position.
Air pollution 1 represents math and science and they represent English was 221 match mortality in match.
Next we look at how we can use a combination of index and match in case of we look up or at local.
Usually we do not need to do this for small datasets but if you have a large dataset we look up will
take up a lot of processing power of Excel you may have to wait for a very long time to get the result.
You may have to look at messages with say excel is not responding etc..
A lighter way to achieve the same objective will be to use index and match combination in this combination
index will return the value desired from the table and match will help us identify the draw number or
the column number of the table from which the values to be written.
For example if you want music marks of student 1 we start typing is equal to
index tab select the reference table.
Note that in the table below the column in which value is to be matched may not be Columbine.
This can be any column and index match.
However for we look up the reference column should always be the first column.
The second parameter is row number which will be returned by match function.
So we type match.
Select student 1
and select the edit below.
Comma 0 what exact match those do bracket comma column number which will be 1
instead.
This gives us the same result as we look up only this is quicker then we look at what dollar sign appropriately
and extend to a it
that's on what the selected Well we covered an important set of lookup homeless and neglected new look
at data sorting filtering and filter data tools.
Thanks.